package com.sm.db;

import java.sql.*;
import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.math.BigDecimal;
import java.util.Random;

/**
 * Author: Sergejs Melderis
 */
public class DbData {

    public static final String CONN_URL =  "jdbc:derby:TestDB";

    public static  void createDatabase() throws Exception {
        System.out.println("Creating database schema");
        DriverManager.getConnection(CONN_URL + ";create=true");
        createTables();
        System.out.println("Schema created");
    }

    public static void createTables() throws Exception {
        InputStream stream = DbData.class.getResourceAsStream("db_model.sql");
        BufferedReader reader = new BufferedReader(new InputStreamReader(stream));
        StringBuilder sb = new StringBuilder();
        String line;
        while ((line = reader.readLine()) != null) {
            sb.append(line);
        }
        String[] sqlDDLs = sb.toString().split(";");
        Connection conn = DriverManager.getConnection(CONN_URL);
        for (String sql : sqlDDLs) {
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            stmt.close();
        }

        conn.close();
    }

    public static void insertTestData() throws Exception {
        System.out.println("Inserting test data...");
        insertUsers();
        insertCategories();
        insertPosts();
        System.out.println("Data inserted");
    }

    public static void insertUsers() {
        BufferedReader in = null;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            InputStream inStream =  DbData.class.getResourceAsStream("users.txt");
            in = new BufferedReader(new InputStreamReader(inStream));
            conn = DriverManager.getConnection(CONN_URL);
            ps = conn.prepareStatement("INSERT INTO USERS VALUES(?,?,?,?,?,?,?)");
            String line;
            DateFormat format = new SimpleDateFormat("mm-dd-yy");
            while ((line = in.readLine()) != null) {
                String []data = line.split(":");
                ps.setInt(1, Integer.parseInt(data[0]));
                for (int i = 1; i < data.length - 1; i++)
                    ps.setString(i + 1, data[i]);
                ps.setDate(7, new Date(format.parse(data[6]).getTime()));
                ps.executeUpdate();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                in.close();
                conn.close();
                ps.close();
            } catch (Exception e) {/*ignored*/}
        }

    }

    public static void insertCategories() {
        BufferedReader in = null;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            InputStream inStream = DbData.class.getResourceAsStream("categories.txt");
            in = new BufferedReader(new InputStreamReader(inStream));
            conn = DriverManager.getConnection(CONN_URL);
            ps = conn.prepareStatement("insert into categories values(?,?,?)");
            String line;
            while ((line = in.readLine()) != null) {
                String []data = line.split(":");
                ps.setLong(1, Long.parseLong(data[0]));
                ps.setString(2, data[1]);
                ps.setString(3, data[2]);
                ps.executeUpdate();
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                in.close();
                conn.close();
                ps.close();
            } catch (Exception e) {/*ignored*/}
        }
    }


    public static void insertPosts() {
        Connection conn = null;
        PreparedStatement postsStmt = null;
        PreparedStatement commentsStmt = null;
        try {
            conn = DriverManager.getConnection(CONN_URL);
            postsStmt = conn.prepareStatement("insert into posts values(?,?,?,?,?)");
            commentsStmt = conn.prepareStatement("insert into comments values(?,?,?,?,?)");
            int commentId = 1;
            for (int postId = 1; postId < 6; postId++) {
                postsStmt.setBigDecimal(1, new BigDecimal(postId));
                postsStmt.setString(2, "Post Content" + postId);
                postsStmt.setInt(3, postId % 4 == 0 ? 4 : postId % 4);
                postsStmt.setTimestamp(4, new Timestamp(postId));
                postsStmt.setLong(5, postId % 3 == 0 ? 3 : postId % 3);
                postsStmt.executeUpdate();
                for (int i = 1; i < 5; i++) {
                    commentsStmt.setBigDecimal(1, new BigDecimal(commentId));
                    commentsStmt.setBigDecimal(2, new BigDecimal(postId));
                    commentsStmt.setString(3, "Comment Content" + commentId);
                    commentsStmt.setDate(4, new Date(commentId));
                    commentsStmt.setString(5, "Author" + commentId);
                    commentsStmt.executeUpdate();
                    commentId++;
                }
            }
            
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                conn.close();
                postsStmt.close();
                commentsStmt.close();
            } catch (Exception e) { /* Ignored */ }
        }
    }


    public static void insertPerformanceTestData() throws Exception {
        System.out.println("Inserting performance test data");
        System.out.println("That is going to take some time...");
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager.getConnection(CONN_URL);
            String sql = "INSERT INTO PerformanceTestTable "
                       + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            Random random = new Random();
            for (int i = 0; i < 50000; i++) {
                stmt.setBigDecimal(1, new BigDecimal(random.nextDouble()));
                stmt.setString(2, String.valueOf(random.nextDouble()));
                stmt.setShort(3, (short) random.nextInt());
                stmt.setInt(4, random.nextInt());
                stmt.setString(5, String.valueOf(random.nextDouble()));
                stmt.setLong(6, random.nextLong());
                stmt.setString(7, String.valueOf(random.nextDouble()));
                stmt.setFloat(8, random.nextFloat());
                stmt.setDouble(9, random.nextDouble());
                stmt.setString(10, String.valueOf(random.nextDouble()));
                stmt.setDate(11, new Date(Math.abs(random.nextLong() % 1135296000000L)));
                stmt.setString(12, String.valueOf(random.nextDouble()));
                stmt.setTime(13, new Time(Math.abs(random.nextLong() % 1135296000000L)));
                stmt.setTimestamp(14, new Timestamp(Math.abs(random.nextLong() % 1135296000000L)));
                stmt.setString(15, String.valueOf(random.nextDouble()));
                stmt.execute();
            }
            System.out.println("Test data inserted");

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            stmt.close();
            conn.close();
        }
    }
}
